﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.OleDb;//引入acesss数据库
using System.Data;
using System.Data.SqlClient;
using System.Web.Security;
using Entity;
namespace Web.admin.major
{
    public partial class InsertAll : System.Web.UI.Page
    {
        //BLL.Students students = new BLL.Students();
        BLL.MajorService majorService = new BLL.MajorService();
        BLL.ClassService classes = new BLL.ClassService();
        protected void Page_Load(object sender, EventArgs e)
        {
        }
        /// <summary>
        /// 将excel表格用access数据库打开
        /// </summary>
        /// <param name="ExcelFileName">文件路径</param>
        /// <param name="ExcelSheet">工作表名称</param>
        /// <returns>返回excel中表格数据</returns>
        protected DataTable ExcelToDb(string ExcelFileName, string ExcelSheet)
        {
            //犹如sqlserver中的sqlconnection
            string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + ExcelFileName + ";" + "Extended Properties=Excel 5.0;";
            //犹如sqlstring，查询语句
            string strExcel = string.Format("select * from [{0}$]", ExcelSheet);
            DataTable dt = new DataTable();
            using (OleDbConnection con = new OleDbConnection(strCon))
            {
                OleDbDataAdapter odda = new OleDbDataAdapter(strExcel, con);
                odda.Fill(dt);
                return dt;
            }
        }
        /// <summary>
        /// 批量导入点击事件    
        /// </summary>
        /// <param name="sender">对象</param>
        /// <param name="e">事件</param>
        protected void btn_up_Click(object sender, EventArgs e)
        {
            BLL.AcademyService academyservice = new BLL.AcademyService();
            BLL.MajorService majorservice = new BLL.MajorService();
            BLL.TeacherService teacherservice = new BLL.TeacherService();
            //判断是否选中文件
            if (ExcelUpLoad.HasFile)
            {
                //得到文件类型
                string fileClass = System.IO.Path.GetExtension(ExcelUpLoad.FileName).ToString();
                if (fileClass == ".xls" || fileClass == ".xlsx")
                {
                    string SavaPath = MapPath("../../Upload").ToString();
                    //如果不存在此路径，则创建
                    if (!System.IO.Directory.Exists(SavaPath))
                    {
                        System.IO.Directory.CreateDirectory(SavaPath);
                    }
                    string name = ExcelUpLoad.FileName;
                    DateTime dtime = DateTime.Now;
                    string newname = dtime.ToString("yyyyMMddHHmmssff") + fileClass;
                    //保存完整路径
                    SavaPath = SavaPath + "\\" + newname;
                    //保存文件
                    ExcelUpLoad.SaveAs(SavaPath);
                    DataTable dt = ExcelToDb(SavaPath, "Sheet1");
                    BoMajor majorModel = new BoMajor();
                    int count = 1;
                    //excel的格式要求，姓名，性别，学号，密码，学院，身份证号
                    foreach (DataRow dr in dt.Rows)
                    {
                        if (!string.IsNullOrEmpty(dr["院系号"].ToString())){
                            BoAcademy academymodel =academyservice.getAcademyByCode(dr["院系号"].ToString());
                            if (academymodel != null)
                                majorModel.MAJ_ACADEMY = academymodel.ACA_ID;
                            else
                            {
                                Response.Write("<script>alert('第" + (count + 1) + "行出现错误,不存在该学院编号')</script>");
                                return;
                            }
                        }
                        else
                        {
                            Response.Write("<script>alert('第" + (count + 1) + "行出现错误,学院编号不允许为空，请检查后再次导入')</script>");
                            return;
                        }
                        if (!string.IsNullOrEmpty(dr["专业号"].ToString()))
                        {
                            if (majorservice.GetMajorByCodeAndAcaCode(dr["专业号"].ToString(), dr["院系号"].ToString()) != null)
                            {
                                Response.Write("<script>alert('第" + (count + 1) + "行出现错误,该专业已经录入')</script>");
                                return;
                            }
                            else
                                majorModel.MAJ_CODE = dr["专业号"].ToString();
                        }
                        else
                        {
                            Response.Write("<script>alert('第" + (count + 1) + "行出现错误,专业号不允许为空，请检查后再次导入')</script>");
                            return;
                        }
                        if (!string.IsNullOrEmpty(dr["专业名"].ToString()))
                            majorModel.MAJ_NAME = dr["专业名"].ToString();
                        else
                        {
                            Response.Write("<script>alert('第" + (count + 1) + "行出现错误,专业名不允许为空，请检查后再次导入')</script>");
                            return;
                        }
                        if (!string.IsNullOrEmpty(dr["学科门类"].ToString()))
                        {
                            majorModel.MAJ_CLASSIFICATION = dr["学科门类"].ToString();
                        }
                        else
                        {
                            majorModel.MAJ_CLASSIFICATION = "";
                        }
                        if (!string.IsNullOrEmpty(dr["专业简称"].ToString()))
                        {
                            majorModel.MAJ_SHORT = dr["专业简称"].ToString();
                        }
                        else
                        {
                            majorModel.MAJ_SHORT = "";
                        }

                        if (!string.IsNullOrEmpty(dr["英文专业名"].ToString()))
                        {
                            majorModel.MAJ_ENNAME = dr["英文专业名"].ToString();
                        }
                        else
                        {
                            majorModel.MAJ_ENNAME = "";
                        }

                        if (!string.IsNullOrEmpty(dr["负责人编号"].ToString()))
                        {
                            BoTeachers teachermodel = teacherservice.GetTeacherByCode(dr["负责人编号"].ToString());
                            if (teachermodel != null)
                                majorModel.MAJ_TEACHER = teachermodel.TEA_ID;
                            else
                            {
                                Response.Write("<script>alert('第" + (count + 1) + "行出现错误,该教师不存在')</script>");
                                return;
                            }
                        }
                        else
                        {
                            majorModel.MAJ_TEACHER = "";
                        }

                        if (!string.IsNullOrEmpty(dr["专业简介"].ToString()))
                        {
                            majorModel.MAJ_CONTENT = dr["专业简介"].ToString();
                        }
                        else
                        {
                            majorModel.MAJ_CONTENT = "";
                        }

                        if (!string.IsNullOrEmpty(dr["备注"].ToString()))
                        {
                            majorModel.MAJ_REMARK = dr["备注"].ToString();
                        }
                        else
                        {
                            majorModel.MAJ_REMARK = "";
                        }

                        if (!string.IsNullOrEmpty(dr["起始学期"].ToString()))
                        {
                            majorModel.MAJ_START = DateTime.Parse(dr["起始学期"].ToString());
                        }
                        majorModel.MAJ_IsShow = true;
                        majorService.CreateMajor(majorModel);
                        count++;
                    }
                    Response.Write("<script>alert('添加成功，共添加" + (count-1).ToString() + "条数据')</script>");

                }
                else
                    //当然这个工作可以在前台做，可以用javascript来做，给upfileload添加一个事件即可
                    Response.Write("<script>alert('请选择正确的excel表格')</script>");
            }
        }

    }
}